<?php
/**
 * Created by 张彪.
 * User: 小马出行
 * Date: 2017/11/2
 * Time: 14:56
 */

namespace app\inter\model;

use \think\Model;
use think\Db;

class OrderCommonModel extends InterBase
{
    public function getOrderCommon($map = array(), $field = 'a.*', $flag = true, $order = 'a.id desc', $page = 1, $limit = 10, $config = '', $is_real = '')
    {
        $config = $config ?: $this->config;
        if($flag) {
            $res = Db::connect($config)->name('order_common a')
                ->join(['inter_car_banci' => 'd'], 'a.banci_id=d.id', 'LEFT')
                ->join(['xm_driver_base' => 'e'], 'a.driver_id=e.driver_id', 'LEFT')
                ->join(['xm_car_info' => 'f'], 'a.car_id=f.car_id', 'LEFT')
                ->join(['inter_order_comment' => 'g'], 'g.common_id=a.id', 'LEFT')
                ->where($map)
                ->where(function ($query) use ($is_real) {
                    $whiteList = Db::name('white_list')->order('id desc')->find();
                    if (!empty($is_real) && (!empty($whiteList['uid_str']) || !empty($whiteList['driver_str']))) {
                        if ($is_real == 1) {            //普通订单
                            $query->whereOr(['a.member_id' => ['not in', $whiteList['uid_str']]]);
                            $query->whereOr(['a.driver_id' => ['not in', $whiteList['driver_str']]]);
                        } elseif ($is_real == 2) {      //测试订单
                            $query->where(['a.member_id' => ['in', $whiteList['uid_str']]]);
                            $query->where(['a.driver_id' => ['in', $whiteList['driver_str']]]);
                        }
                    }
                })
                ->field($field)->order($order)->page($page, $limit)->select();
//            var_dump(Db::connect($config)->name('order_common a')->getLastSql());exit();
        } else {
            $res = Db::connect($config)->name('order_common a')
                ->join('__CAR_BANCI__ d', 'a.banci_id=d.id', 'LEFT')->join(['xm_driver_base' => 'e'], 'a.driver_id=e.driver_id', 'LEFT')
                ->join(['xm_car_info' => 'f'], 'a.car_id=f.car_id', 'LEFT')->join('__ORDER_COMMENT__ g', 'g.common_id=a.id', 'LEFT')
                ->where($map)->field($field)->find();
        }

        return $res;

//        return $flag ? Db::connect($config)->name('order_common a')
//            ->join('__CAR_BANCI__ d', 'a.banci_id=d.id', 'LEFT')
//            ->join(['xm_driver_base' => 'e'], 'a.driver_id=e.driver_id', 'LEFT')
//            ->join(['xm_car_info' => 'f'], 'a.car_id=f.car_id', 'LEFT')
//            ->join('__ORDER_COMMENT__ g', 'g.common_id=a.id', 'LEFT')
////            ->join(['xm_user'=>'u'],'u.uid = a.member_id','LEFT')
//            ->where($map)->field($field)->order($order)->page($page, $limit)->select()
//            : Db::connect($config)->name('order_common a')
//                ->join('__CAR_BANCI__ d', 'a.banci_id=d.id', 'LEFT')->join(['xm_driver_base' => 'e'], 'a.driver_id=e.driver_id', 'LEFT')
//                ->join(['xm_car_info' => 'f'], 'a.car_id=f.car_id', 'LEFT')->join('__ORDER_COMMENT__ g', 'g.common_id=a.id', 'LEFT')
//                ->where($map)->field($field)->find();
    }

    public function addOrderCommon($data)
    {
        return Db::table('inter_order_common')->insertGetId($data);
    }

    public function updateOrderCommon($map = array(), $data)
    {
        return Db::table('inter_order_common')->where($map)->update($data);
    }

    public function getOrderCommonDetail($map = array(), $field = 'a.*', $flag = true, $order = 'a.id desc', $page = 1, $limit = 10, $config = '')
    {
        $config = $config ?: $this->config;
        return $flag ? Db::connect($config)->name('order_common a')
            ->join(config('inner_db')['database'] . '.xm_user b', 'a.member_id = b.uid', 'LEFT')
            ->join(config('inner_db')['database'] . '.xm_coupon c', 'a.coupon_id = c.coupon_id', 'LEFT')
            ->join('__CAR_BANCI__ d', 'a.banci_id=d.id', 'LEFT')
            ->join(['xm_driver_base' => 'e'], 'a.driver_id=e.driver_id', 'LEFT')
            ->join(['xm_driver_info' => 'h'], 'e.driver_id=h.driver_id', 'LEFT')
            ->join(['xm_car_info' => 'f'], 'a.car_id=f.car_id', 'LEFT')
            ->join('__ORDER_COMMENT__ g', 'g.common_id=a.id', 'LEFT')
            ->where($map)->field($field)->order($order)->page($page, $limit)->select()
            : Db::connect($config)->name('order_common a')
                ->join(config('inner_db')['database'] . '.xm_user b', 'a.member_id = b.uid', 'LEFT')
                ->join(config('inner_db')['database'] . '.xm_coupon c', 'a.coupon_id = c.coupon_id', 'LEFT')
                ->join('__CAR_BANCI__ d', 'a.banci_id=d.id', 'LEFT')
                ->join(['xm_driver_base' => 'e'], 'a.driver_id=e.driver_id', 'LEFT')
                ->join(['xm_driver_info' => 'h'], 'e.driver_id=h.driver_id', 'LEFT')
                ->join(['xm_car_info' => 'f'], 'a.car_id=f.car_id', 'LEFT')
                ->join('__ORDER_COMMENT__ g', 'g.common_id=a.id', 'LEFT')
                ->where($map)->field($field)->find();
    }

    //24小时订单数据
    public static function totalOrderNumber($dateArray=[]){
        $where = [];
        //查询设置 - 测试账号信息
        $whiteList = Db::name('white_list')->order('id desc')->find();

        $where='';
        if(!empty($dateArray['all_order']) && (!empty($whiteList['uid_str']) || !empty($whiteList['driver_str']))){
            if($dateArray['all_order'] == 1){
                /*$where['uid'] = ['not in',$whiteList['uid_str']];
                $where['driver_id'] = ['not in',$whiteList['driver_str']];      //普通数据*/

                $where .=' and uid not in('.$whiteList['uid_str'].')';
                $where .=' and driver_id not in('.$whiteList['driver_str'].')';

            }elseif ($dateArray['all_order'] == 2){
                /*$where['uid'] = ['in',$whiteList['uid_str']];
                $where['driver_id'] = ['in',$whiteList['driver_str']];          //测试数据*/

                $where .=' and uid in('.$whiteList['uid_str'].')';
                $where .=' and driver_id in('.$whiteList['driver_str'].')';
            }
        }
        /*$orderInfo=[];
        //24小时拆分
        for ($i = 0; $i <= 23; $i++) {
            foreach ($dateArray as $key => $value) {
                if (strlen($i) == 1) {
                    $i = '0' . $i;
                }
                $start_time = $value." ". $i .":00:00";
                $end_time   = $value." ". $i .":59:59";
                $where['order_time']    = ['between',[strtotime($start_time),strtotime($end_time)]];
                $where['is_del']        = 2;
                $orderInfo[$i][]        = Db::table('inter_order_common')->where($where)->count();
            }

        }
        foreach ($orderInfo as $key=>$value){
            $sumOrderNum = 0;
            foreach ($value as $val) {
                $sumOrderNum += $val;
            }
            $arr[] = $sumOrderNum;
        }*/

        $times=time();
        $start_time = date("Y-m-d",$times);
        $end_time   = date("Y-m-d",strtotime('+1 day',$times));
        $sql="SELECT timetable. HOUR HOUR, ifnull(sumtable.count, 0) Count FROM ( SELECT a.region_id - 1 AS HOUR FROM xm_region a ORDER BY a.region_id ASC LIMIT 24 ) timetable LEFT JOIN ( SELECT HOUR (FROM_UNIXTIME(b.order_time, '%Y-%m-%d %H')) HOUR, count(b.id) count FROM inter_order_common b WHERE b.is_del = 2 and FROM_UNIXTIME(b.order_time, '%Y-%m-%d') between '".$start_time."' and '".$end_time."'".$where." GROUP BY date_format(b.order_time, '%Y%m%d-%H'), HOUR ) sumtable ON timetable. HOUR = sumtable. HOUR ORDER BY HOUR";

        $orderInfo = Db::query($sql);
        $arr=array();
        foreach($orderInfo as $key=>$value){
            $arr[$value['HOUR']]=$value['Count'];
        }

        $sumOrderNumStr = implode(',', $arr);
        return $sumOrderNumStr;
    }

    //城际车数据统计图表
    public function totalInfo($dateArray = [], $all_order = '', $fleet_id = '')
    {
        //查询设置 - 测试账号信息
        $whiteList = Db::name('white_list')->order('id desc')->find();
        if (!empty($all_order) && (!empty($whiteList['uid_str']) || !empty($whiteList['driver_str']))) {
            if ($all_order == 1) {
                $where['member_id'] = ['not in', $whiteList['uid_str']];
                $where['driver_id'] = ['not in', $whiteList['driver_str']];      //普通订单
            } elseif ($all_order == 2) {
                $where['member_id'] = ['in', $whiteList['uid_str']];
                $where['driver_id'] = ['in', $whiteList['driver_str']];          //测试订单
            }
        }

        if ($fleet_id) {
            $where['fleet_id'] = $fleet_id;
        }
        //循环查询订单数据
        foreach ($dateArray as $key => $value) {
            $start_time = strtotime($value);
            $end_time = strtotime($value . ' 23:59:59');
            if (!empty($start_time) && !empty($end_time)) {
                $where['order_time'] = ['between', [$start_time, $end_time]];
            }
            $where['is_del'] = 2;
            //包车订单数member_id
            $orderInfo[$value]['is_contract'] = Db::table('inter_order_common')->where($where)->where('is_contract', '=', 1)->count();


            //拼车订单数
            $orderInfo[$value]['no_contract'] = Db::table('inter_order_common')->where($where)->where('is_contract', '=', 0)->count();

            //订单总数
            $orderInfo[$value]['count'] = Db::table('inter_order_common')->where($where)->count();
//            halt(Db::table('inter_order_common')->getLastSql());
            //时时订单
            $orderInfo[$value]['time_count'] = Db::table('inter_order_common')->where($where)->count();
            //预约订单
            $orderInfo[$value]['make_count'] = Db::table('inter_order_common')->where($where)->count();
            //取消订单数量
            $orderInfo[$value]['cancel_count'] = Db::table('inter_order_common')->where($where)->where(['order_status' => ['in', [25,11,12,10,20,15]]])->count();
            //待接单数量
            $orderInfo[$value]['wait_count'] = Db::table('inter_order_common')->where($where)->where(['order_status' => ['in', [0]]])->count();
            //已接单数量
            $orderInfo[$value]['take_count'] = Db::table('inter_order_common')->where($where)->where(['order_status' => ['between', [16, 30]]])->count();
            //已完成数量
            $orderInfo[$value]['finish_count'] = Db::table('inter_order_common')->where($where)->where(['order_status' => ['>=',40]])->count();
            //查询已完成订单数量金额统计
            $finishList = Db::table('inter_order_common')->where($where)->where(['order_status' => ['>=', 45]])->select();

            $is_contract = Db::table('inter_order_common')->where($where)->where('is_contract', '=', 1)->select();

            $no_contract = Db::table('inter_order_common')->where($where)->where('is_contract', '=', 0)->where(['order_status' => ['>=', 45]])->select();


            //包车与否的金额变换
            if (!empty($is_contract)) {
                $need_price = $coupon_price = '0';
                foreach ($is_contract as $kly => $val) {
                    $need_price += $val['need_price'];
                    $coupon_price += $val['coupon_price'];
                }
                $orderInfo[$value]['is_contract_price'] = sprintf("%.2f", $need_price);
                $orderInfo[$value]['is_contract_coupon'] = sprintf("%.2f", $coupon_price);
            } else {
                $orderInfo[$value]['is_contract_price'] = 0;
                $orderInfo[$value]['is_contract_coupon'] = 0;
            }
            if (!empty($no_contract)) {
                $need_price = $coupon_price = '0';
                foreach ($no_contract as $kly => $val) {
                    $need_price += $val['need_price'];
                    $coupon_price += $val['coupon_price'];
                }
                $orderInfo[$value]['no_contract_price'] = sprintf("%.2f", $need_price);
                $orderInfo[$value]['no_contract_coupon'] = sprintf("%.2f", $coupon_price);
            } else {
                $orderInfo[$value]['no_contract_price'] = 0;
                $orderInfo[$value]['no_contract_coupon'] = 0;
            }
            if (!empty($finishList)) {
                $order_price = $need_price = $coupon_price = '0';
                foreach ($finishList as $kly => $val) {
                    $need_price += $val['need_price'];
                    $coupon_price += $val['coupon_price'];
                    $order_price += $val['order_price'];
                }
                $orderInfo[$value]['need_price'] = sprintf("%.2f", $need_price);
                $orderInfo[$value]['coupon_price'] = sprintf("%.2f", $coupon_price);
                $orderInfo[$value]['order_price'] = sprintf("%.2f", $order_price);
            } else {
                $orderInfo[$value]['need_price'] = 0;
                $orderInfo[$value]['coupon_price'] = 0;
                $orderInfo[$value]['order_price'] = 0;

            }
        }
        $result = [];
        foreach ($orderInfo as $key => $value) {
            $result['count'] .= $value['count'] . ',';
            $result['time_count'] .= $value['time_count'] . ',';
            $result['make_count'] .= $value['make_count'] . ',';
            $result['cancel_count'] .= $value['cancel_count'] . ',';
            $result['wait_count'] .= $value['wait_count'] . ',';
            $result['take_count'] .= $value['take_count'] . ',';
            $result['finish_count'] .= $value['finish_count'] . ',';
            $result['need_price'] .= $value['need_price'] . ',';
            $result['order_price'] .= $value['order_price'] . ',';
            $result['coupon_price'] .= $value['coupon_price'] . ',';
            $result['is_contract'] .= $value['is_contract'] . ',';
            $result['no_contract'] .= $value['no_contract'] . ',';
            $result['no_contract_price'] .= $value['no_contract_price'] . ',';
            $result['no_contract_coupon'] .= $value['no_contract_coupon'] . ',';
            $result['is_contract_coupon'] .= $value['is_contract_coupon'] . ',';
            $result['is_contract_price'] .= $value['is_contract_price'] . ',';
        }
        return $result;
    }

    //查询高峰期订单数量
    public function timeOrderNum($dateArray = [], $all_order = '', $fleet_id = '')
    {
        if ($fleet_id) {
            $where['fleet_id'] = $fleet_id;
        }
        //查询设置 - 测试账号信息
        $whiteList = Db::name('white_list')->order('id desc')->find();
        if (!empty($all_order) && (!empty($whiteList['uid_str']) || !empty($whiteList['driver_str']))) {
            if ($all_order == 1) {
                $where['member_id'] = ['not in', $whiteList['uid_str']];
                $where['driver_id'] = ['not in', $whiteList['driver_str']];      //普通订单
            } elseif ($all_order == 2) {
                $where['member_id'] = ['in', $whiteList['uid_str']];
                $where['driver_id'] = ['in', $whiteList['driver_str']];          //测试订单
            }
        }
        for ($i = 0; $i <= 23; $i++) {
            foreach ($dateArray as $key => $value) {
                if (strlen($i) == 1) {
                    $i = '0' . $i;
                }
                $start_time = $value . " " . $i . ":00:00";
                $end_time = $value . " " . $i . ":59:59";
                $where['order_time'] = ['between', [strtotime($start_time), strtotime($end_time)]];
                $where['is_del'] = 2;
                $orderInfo[$i][] = Db::table('inter_order_common')->where($where)->count();
            }
        }
        foreach ($orderInfo as $key => $value) {
            $sumOrderNum = 0;
            foreach ($value as $val) {
                $sumOrderNum += $val;
            }
            $arr[] = $sumOrderNum;
        }
        $sumOrderNumStr = implode(',', $arr);
        return $sumOrderNumStr;
    }

}